﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.ComponentModel;

namespace WebApplication10.App_Code
{
    //[DataObject]
    public class Job
    {
        //[DataObjectField(true, true)]
        public int JobID { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public int DateOfPost { get; set; }
        public int MonthOfPost { get; set; }
        public int YearOfPost { get; set; }
        public int EmployerID { get; set; }
        public string CompanyName { get; set; }
        public int CompanyID { get; set; }
        public string CloseDate { get; set; }
        public string HighestQualification { get; set; }
        public int YearsOfWorkExperience { get; set; }
        public string Category { get; set; }

        //[DataObjectMethod(DataObjectMethodType.Select, true)]
        public static DataTable GetJobByTitle(string Title)
        {
            SqlConnection conn = null;
            DataTable jt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Job where Title=@Title";
                comm.Parameters.AddWithValue("@Title", Title);
                SqlDataReader dr = comm.ExecuteReader();
                jt = new DataTable();
                jt.Load(dr);
                dr.Close();



            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return jt;
        }
        public static List<Job> GetJobByTtile()
        {
            SqlConnection conn = new SqlConnection();
            List<Job> results = new List<Job>();
            try
            {
                conn = new SqlConnection();
                string constr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ConnectionString;
                conn.ConnectionString = constr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from Job";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Job j = new Job();
                    j.Title = (string)dr["Title"];
                    j.JobID = (int)dr["JobID"];
                    j.DateOfPost = (int)dr["DateOfPost"];
                    j.MonthOfPost = (int)dr["MonthOfPost"];
                    j.YearOfPost = (int)dr["YearOfPost"];
                    //j.EmployerID = (int)dr["EmployerID"];
                    //j.Category = (string)dr["JobCategoryID"];
                    j.CompanyName = (string)dr["CompanyName"];
                    j.CompanyID = (int)dr["CompanyID"];
                    //j.CloseDate = (string)dr["CloseDate"];
                    j.HighestQualification = (string)dr["HighestQualification"];
                    j.YearsOfWorkExperience = (int)dr["YearsOfWorkExperience"];
                    results.Add(j);

                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();

            }
            return results;
        }
        public static Job GetJobByID(string JobID)
        {
            SqlConnection conn = new SqlConnection();
            Job j = null;
            try
            {
                conn = new SqlConnection();
                string constr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = constr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from Job where JobID=@JobID";
                comm.Parameters.AddWithValue("@JobID", JobID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    j = new Job();
                    j.Title = (string)dr["Title"];
                    j.JobID = (int)dr["JobID"];
                    j.DateOfPost = (int)dr["DateOfPost"];
                    j.MonthOfPost = (int)dr["MonthOfPost"];
                    j.YearOfPost = (int)dr["YearOfPost"];
                    //j.EmployerID = (int)dr["EmployerID"];
                    //j.Category = (string)dr["JobCategoryID"];
                    j.CompanyName = (string)dr["CompanyName"];
                    j.CompanyID = (int)dr["CompanyID"];
                    //j.CloseDate = (string)dr["CloseDate"];
                    j.HighestQualification = (string)dr["HighestQualification"];
                    j.YearsOfWorkExperience = (int)dr["YearsOfWorkExperience"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return j;
        }

        public static void UpdateJob(Job j)//update
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = new SqlConnection();
                string constr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ConnectionString;
                conn.ConnectionString = constr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Update Job set Title=@Title, DateOfPost = @DateOfPost where JobID = @JobID";
                comm.Parameters.AddWithValue("@Title", j.Title);
                comm.Parameters.AddWithValue("@DateOfPost", j.DateOfPost);
                comm.Parameters.AddWithValue("@JobID", j.JobID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static void DeleteJob(Job j) // Delete.
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Delete from Job WHERE JobID=@JobID";
                comm.Parameters.AddWithValue("@JobID", j.JobID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static int InsertJob(Job j) // Insert.
        {
            SqlConnection conn = null;
            int newJobID = 0;

            try
            {
                conn = new SqlConnection ();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT into Job(Title, CompanyID, CompanyName, Description, HighestQualification, YearsOfWorkExperience, Category, DateOfPost, MonthOfPost, YearOfPost) VALUES (@Title, @CompanyID, @CompanyName, @Description, @HighestQualification, @YearsOfWorkExperience, @Category, @DateOfPost, @MonthOfPost, @YearOfPost)";
                comm.Parameters.AddWithValue("@Title", j.Title);
                comm.Parameters.AddWithValue("@CompanyID", j.CompanyID);
                comm.Parameters.AddWithValue("@CompanyName", j.CompanyName);
                comm.Parameters.AddWithValue("@Description", j.Description);
                comm.Parameters.AddWithValue("@HighestQualification", j.HighestQualification);
                comm.Parameters.AddWithValue("@YearsOfWorkExperience", j.YearsOfWorkExperience);
                comm.Parameters.AddWithValue("@Category", j.Category);
                comm.Parameters.AddWithValue("@DateOfPost", j.DateOfPost);
                comm.Parameters.AddWithValue("@MonthOfPost", j.MonthOfPost);
                comm.Parameters.AddWithValue("@YearOfPost", j.YearOfPost);

                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newJobID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newJobID = int.Parse(dr["NewJobID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;

            }
            finally
            {
                conn.Close();
            }
            return newJobID;
        }

    }
}